﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;

namespace QuanlyDanhsachThiCapPhong
{
    class Truong
    {
        string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\\Database\\Data.dat';Extended Properties=Excel 8.0";
        Function func = new Function();
        public List<string> danhsachTruong()
        {
            List<string> ds = new List<string>();
            OleDbConnection conn = new OleDbConnection(connstr);
            conn.Open();
            string query = "SELECT [Tên] FROM [Truong$] WHERE ID <> NULL";
            OleDbCommand cmd = new OleDbCommand(query, conn);
            OleDbDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                ds.Add(dr.GetString(0));
            }
            conn.Close();
            return ds;
        }
        public DataTable ListTruong()
        {
            List<string> ds = new List<string>();
            OleDbConnection conn = new OleDbConnection(connstr);
            conn.Open();
            string query = "SELECT [Tên], [Hội đồng thi] FROM [Truong$] WHERE ID <> NULL";
            OleDbCommand cmd = new OleDbCommand(query, conn);
            OleDbDataAdapter da = new OleDbDataAdapter();
            da.SelectCommand = cmd;
            DataTable dt = new DataTable();
            da.Fill(dt);
            conn.Close();
            return dt;
        }
        public void ThemHoiDong(string hoidong, string truong)
        {
            try
            {
                OleDbConnection conn = new OleDbConnection(connstr);
                conn.Open();
                String query = "UPDATE [Truong$] SET [Hội đồng thi] = @hoidong WHERE [Tên] = @tentruong";
                OleDbCommand cmd = new OleDbCommand(query, conn);
                cmd.Parameters.AddWithValue("@hoidong", SqlDbType.VarChar);
                cmd.Parameters.AddWithValue("@tentruong", SqlDbType.VarChar);

                cmd.Parameters["@hoidong"].Value = func.checkNull(hoidong);
                cmd.Parameters["@tentruong"].Value = func.checkNull(truong);
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex) { }
        }
    }
}
